// SMSLib for Java v3
// A Java API library for sending and receiving SMS via a GSM modem
// or other supported gateways.
// Web Site: http://www.smslib.org
//
// Copyright (C) 2002-2012, Thanasis Delenikas, Athens/GREECE.
// SMSLib is distributed under the terms of the Apache License version 2.0
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package org.smslib.smsserver.interfaces;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Properties;
import org.smslib.InboundMessage;
import org.smslib.OutboundBinaryMessage;
import org.smslib.OutboundMessage;
import org.smslib.OutboundWapSIMessage;
import org.smslib.StatusReportMessage;
import org.smslib.Message.MessageEncodings;
import org.smslib.Message.MessageTypes;
import org.smslib.OutboundMessage.FailureCauses;
import org.smslib.OutboundMessage.MessageStatuses;
import org.smslib.OutboundWapSIMessage.WapSISignals;
import org.smslib.helper.Logger;
import org.smslib.smsserver.SMSServer;

/**
 * Interface for database communication with SMSServer. <br />
 * Inbound messages and calls are logged in special tables, outbound messages
 * are retrieved from another table.
 */
public class Database extends Interface<Integer> {
	static final int SQL_DELAY = 1000;

	int sqlDelayMultiplier = 1;

	private Connection dbCon = null;

	public Database(String myInterfaceId, Properties myProps,
			SMSServer myServer, InterfaceTypes myType) {
		super(myInterfaceId, myProps, myServer, myType);
		setDescription("Default database interface.");
	}

	@Override
	public void start() throws Exception {
		Connection con = null;
		Statement cmd;
		Class.forName(getProperty("driver"));
		while (true) {
			try {
				con = getDbConnection();
				cmd = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
				cmd.executeUpdate("update "
						+ getProperty("tables.sms_out", "smsserver_out")
						+ " set status = 'U' where status = 'Q'");
				con.commit();
				cmd.close();
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				if (getServer().getShutdown())
					break;
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
		super.start();
	}

	@Override
	public void stop() throws Exception {
		Connection con = null;
		while (true) {
			try {
				Statement cmd;
				con = getDbConnection();
				cmd = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
				cmd.executeUpdate("update "
						+ getProperty("tables.sms_out", "smsserver_out")
						+ " set status = 'U' where status = 'Q'");
				con.commit();
				cmd.close();
				closeDbConnection();
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				if (getServer().getShutdown())
					break;
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
		super.stop();
	}

	@Override
	public void callReceived(String gtwId, String callerId) throws Exception {
		Connection con = null;
		while (true) {
			try {
				PreparedStatement cmd;
				con = getDbConnection();
				cmd = con
						.prepareStatement("insert into "
								+ getProperty("tables.calls", "smsserver_calls")
								+ " (call_date, gateway_id, caller_id) values (?,?,?) ");
				cmd.setTimestamp(1,
						new Timestamp(new java.util.Date().getTime()));
				cmd.setString(2, gtwId);
				cmd.setString(3, callerId);
				cmd.executeUpdate();
				con.commit();
				cmd.close();
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
	}

	@Override
	public void messagesReceived(Collection<InboundMessage> msgList)
			throws Exception {
		Connection con = null;
		while (true) {
			try {
				PreparedStatement pst;
				con = getDbConnection();
				pst = con
						.prepareStatement(" insert into "
								+ getProperty("tables.sms_in", "smsserver_in")
								+ " (process, originator, type, encoding, message_date, receive_date, text,"
								+ " original_ref_no, original_receive_date, gateway_id) "
								+ " values(?,?,?,?,?,?,?,?,?,?)");
				for (InboundMessage msg : msgList) {
					if ((msg.getType() == MessageTypes.INBOUND)
							|| (msg.getType() == MessageTypes.STATUSREPORT)) {
						pst.setInt(1, 0);
						switch (msg.getEncoding()) {
						case ENC7BIT:
							pst.setString(4, "7");
							break;
						case ENC8BIT:
							pst.setString(4, "8");
							break;
						case ENCUCS2:
							pst.setString(4, "U");
							break;
						case ENCCUSTOM:
							pst.setString(4, "C");
							break;
						}
						switch (msg.getType()) {
						case INBOUND:
							pst.setString(3, "I");
							pst.setString(2, msg.getOriginator());
							if (msg.getDate() != null)
								pst.setTimestamp(5, new Timestamp(msg.getDate()
										.getTime()));
							pst.setString(8, null);
							pst.setTimestamp(9, null);
							break;
						case STATUSREPORT:
							pst.setString(3, "S");
							pst.setString(2,
									((StatusReportMessage) msg).getRecipient());
							if (((StatusReportMessage) msg).getSent() != null)
								pst.setTimestamp(5, new Timestamp(
										((StatusReportMessage) msg).getSent()
												.getTime()));
							pst.setString(8,
									((StatusReportMessage) msg).getRefNo());
							if (((StatusReportMessage) msg).getReceived() != null)
								pst.setTimestamp(9, new Timestamp(
										((StatusReportMessage) msg)
												.getReceived().getTime()));
							if (getProperty("update_outbound_on_statusreport",
									"no").equalsIgnoreCase("yes")) {
								PreparedStatement cmd2;
								cmd2 = con
										.prepareStatement(" update "
												+ getProperty("tables.sms_out",
														"smsserver_out")
												+ " set status = ? "
												+ " where (recipient = ? or recipient = ?) and ref_no = ? and gateway_id = ?");
								switch (((StatusReportMessage) msg).getStatus()) {
								case DELIVERED:
									cmd2.setString(1, "D");
									break;
								case KEEPTRYING:
									cmd2.setString(1, "P");
									break;
								case ABORTED:
									cmd2.setString(1, "A");
									break;
								case UNKNOWN:
									break;
								}
								cmd2.setString(2, ((StatusReportMessage) msg)
										.getRecipient());
								if (((StatusReportMessage) msg).getRecipient()
										.startsWith("+"))
									cmd2.setString(3,
											((StatusReportMessage) msg)
													.getRecipient()
													.substring(1));
								else
									cmd2.setString(
											3,
											"+"
													+ ((StatusReportMessage) msg)
															.getRecipient());
								cmd2.setString(4,
										((StatusReportMessage) msg).getRefNo());
								cmd2.setString(5, ((StatusReportMessage) msg)
										.getGatewayId());
								cmd2.executeUpdate();
								cmd2.close();
							}
							break;
						default:
							break;
						}
						pst.setTimestamp(6,
								new Timestamp(new java.util.Date().getTime()));
						if (msg.getEncoding() == MessageEncodings.ENC8BIT)
							pst.setString(7, msg.getPduUserData());
						else
							pst.setString(7, (msg.getText().length() == 0 ? ""
									: msg.getText()));
						pst.setString(10, msg.getGatewayId());
						pst.executeUpdate();
					}
				}
				pst.close();
				con.commit();
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
	}

	@Override
	public Collection<OutboundMessage> getMessagesToSend() throws Exception {
		Connection con = null;
		Collection<OutboundMessage> msgList = new ArrayList<OutboundMessage>();
		while (true) {
			try {
				OutboundMessage msg;
				Statement cmd;
				PreparedStatement pst;
				ResultSet rs;
				int msgCount;
				msgCount = 1;
				con = getDbConnection();
				cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_UPDATABLE);
				pst = con.prepareStatement("update "
						+ getProperty("tables.sms_out", "smsserver_out")
						+ " set status = 'Q' where id = ? ");
				rs = cmd.executeQuery("select id, type, recipient, text, wap_url, wap_expiry_date, wap_signal, create_date, originator, encoding, status_report, flash_sms, src_port, dst_port, sent_date, ref_no, priority, status, errors, gateway_id from "
						+ getProperty("tables.sms_out", "smsserver_out")
						+ " where status = 'U' order by priority desc, id");
				while (rs.next()) {
					if (msgCount > Integer.parseInt(getProperty("batch_size")))
						break;
					if (getServer().checkPriorityTimeFrame(
							rs.getInt("priority"))) {
						switch (rs.getString("type").charAt(0)) {
						case 'O':
							switch (rs.getString("encoding").charAt(0)) {
							case '7':
								msg = new OutboundMessage(rs.getString(
										"recipient").trim(), rs.getString(
										"text").trim());
								msg.setEncoding(MessageEncodings.ENC7BIT);
								break;
							case '8': {
								String text = rs.getString("text").trim();
								byte bytes[] = new byte[text.length() / 2];
								for (int i = 0; i < text.length(); i += 2) {
									int value = (Integer.parseInt(
											"" + text.charAt(i), 16) * 16)
											+ (Integer
													.parseInt(
															""
																	+ text.charAt(i + 1),
															16));
									bytes[i / 2] = (byte) value;
								}
								msg = new OutboundBinaryMessage(rs.getString(
										"recipient").trim(), bytes);
							}
								break;
							case 'U':
								msg = new OutboundMessage(rs.getString(
										"recipient").trim(), rs.getString(
										"text").trim());
								msg.setEncoding(MessageEncodings.ENCUCS2);
								break;
							default:
								msg = new OutboundMessage(rs.getString(
										"recipient").trim(), rs.getString(
										"text").trim());
								msg.setEncoding(MessageEncodings.ENC7BIT);
								break;
							}
							if (rs.getInt("flash_sms") == 1)
								msg.setFlashSms(true);
							if (rs.getInt("src_port") != -1) {
								msg.setSrcPort(rs.getInt("src_port"));
								msg.setDstPort(rs.getInt("dst_port"));
							}
							break;
						case 'W':
							Date wapExpiryDate;
							WapSISignals wapSignal;
							if (rs.getTime("wap_expiry_date") == null) {
								Calendar cal = Calendar.getInstance();
								cal.setTime(new Date());
								cal.add(Calendar.DAY_OF_YEAR, 7);
								wapExpiryDate = cal.getTime();
							} else
								wapExpiryDate = rs
										.getTimestamp("wap_expiry_date");
							if (rs.getString("wap_signal") == null)
								wapSignal = WapSISignals.NONE;
							else {
								switch (rs.getString("wap_signal").charAt(0)) {
								case 'N':
									wapSignal = WapSISignals.NONE;
									break;
								case 'L':
									wapSignal = WapSISignals.LOW;
									break;
								case 'M':
									wapSignal = WapSISignals.MEDIUM;
									break;
								case 'H':
									wapSignal = WapSISignals.HIGH;
									break;
								case 'D':
									wapSignal = WapSISignals.DELETE;
									break;
								default:
									wapSignal = WapSISignals.NONE;
								}
							}
							msg = new OutboundWapSIMessage(rs.getString(
									"recipient").trim(), new URL(rs.getString(
									"wap_url").trim()), rs.getString("text")
									.trim(), wapExpiryDate, wapSignal);
							break;
						default:
							throw new Exception("Message type '"
									+ rs.getString("type") + "' is unknown!");
						}
						msg.setPriority(rs.getInt("priority"));
						if (rs.getInt("status_report") == 1)
							msg.setStatusReport(true);
						if ((rs.getString("originator") != null)
								&& (rs.getString("originator").length() > 0))
							msg.setFrom(rs.getString("originator").trim());
						msg.setGatewayId(rs.getString("gateway_id").trim());
						msgList.add(msg);
						getMessageCache().put(msg.getMessageId(),
								rs.getInt("id"));
						pst.setInt(1, rs.getInt("id"));
						pst.executeUpdate();
						con.commit();
						msgCount++;
					}
				}
				con.commit();
				rs.close();
				cmd.close();
				pst.close();
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
		return msgList;
	}

	@Override
	public int getPendingMessagesToSend() throws Exception {
		Connection con = null;
		int count = -1;
		while (true) {
			try {
				Statement cmd;
				ResultSet rs;
				con = getDbConnection();
				cmd = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = cmd.executeQuery("select count(*) as cnt from "
						+ getProperty("tables.sms_out", "smsserver_out")
						+ " where status in ('U', 'Q')");
				if (rs.next())
					count = rs.getInt("cnt");
				rs.close();
				cmd.close();
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
		return count;
	}

	@Override
	public void markMessage(OutboundMessage msg) throws Exception {
		Connection con = null;
		if (getMessageCache().get(msg.getMessageId()) == null)
			return;
		while (true) {
			try {
				PreparedStatement selectStatement, updateStatement;
				ResultSet rs;
				int errors;
				con = getDbConnection();
				selectStatement = con.prepareStatement("select errors from "
						+ getProperty("tables.sms_out", "smsserver_out")
						+ " where id = ?");
				selectStatement.setInt(1,
						getMessageCache().get(msg.getMessageId()));
				rs = selectStatement.executeQuery();
				rs.next();
				errors = rs.getInt("errors");
				rs.close();
				selectStatement.close();
				if (msg.getMessageStatus() == MessageStatuses.SENT) {
					updateStatement = con
							.prepareStatement("update "
									+ getProperty("tables.sms_out",
											"smsserver_out")
									+ " set status = ?, sent_date = ?, gateway_id = ?, ref_no = ? where id = ?");
					updateStatement.setString(1, "S");
					updateStatement.setTimestamp(2, new Timestamp(msg
							.getDispatchDate().getTime()));
					updateStatement.setString(3, msg.getGatewayId());
					updateStatement.setString(4, msg.getRefNo());
					updateStatement.setInt(5,
							getMessageCache().get(msg.getMessageId()));
					updateStatement.executeUpdate();
					con.commit();
					updateStatement.close();
				} else if ((msg.getMessageStatus() == MessageStatuses.UNSENT)
						|| ((msg.getMessageStatus() == MessageStatuses.FAILED) && (msg
								.getFailureCause() == FailureCauses.NO_ROUTE))) {
					updateStatement = con.prepareStatement("update "
							+ getProperty("tables.sms_out", "smsserver_out")
							+ " set status = ? where id = ?");
					updateStatement.setString(1, "U");
					updateStatement.setInt(2,
							getMessageCache().get(msg.getMessageId()));
					updateStatement.executeUpdate();
					con.commit();
					updateStatement.close();
				} else {
					updateStatement = con.prepareStatement("update "
							+ getProperty("tables.sms_out", "smsserver_out")
							+ " set status = ?, errors = ? where id = ?");
					errors++;
					if (errors > Integer.parseInt(getProperty("retries", "2")))
						updateStatement.setString(1, "F");
					else
						updateStatement.setString(1, "U");
					updateStatement.setInt(2, errors);
					updateStatement.setInt(3,
							getMessageCache().get(msg.getMessageId()));
					updateStatement.executeUpdate();
					con.commit();
					updateStatement.close();
				}
				break;
			} catch (SQLException e) {
				try {
					if (con != null)
						con.close();
					closeDbConnection();
				} catch (Exception innerE) {
				}
				Logger.getInstance().logError(
						String.format(
								"SQL failure, will retry in %d seconds...",
								(sqlDelayMultiplier * (SQL_DELAY / 1000))), e,
						null);
				Thread.sleep(sqlDelayMultiplier * SQL_DELAY);
				sqlDelayMultiplier *= 2;
			}
		}
		getMessageCache().remove(msg.getMessageId());
	}

	private Connection getDbConnection() throws SQLException {
		if (dbCon == null) {
			dbCon = DriverManager.getConnection(getProperty("url"),
					getProperty("username", ""), getProperty("password", ""));
			dbCon.setAutoCommit(false);
			sqlDelayMultiplier = 1;
		}
		return dbCon;
	}

	private void closeDbConnection() {
		try {
			if (dbCon != null)
				dbCon.close();
		} catch (Exception e) {
		} finally {
			dbCon = null;
		}
	}
}
